package com.biubiushop.util;

import cn.hutool.core.collection.CollUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * 
 * Created by chenjia on 2019-06-04
 *
 */
//@Component
public class JDBCTemplate {

	@Resource
	JdbcTemplate jdbcTemplate;

	private static Pattern p = Pattern.compile("order\\s*by[\\w|\\W|\\s|\\S]*", Pattern.CASE_INSENSITIVE);

	/**
	 * 将查询返回结果集封装为分页List<Vo>
	 * @return
	 */
	public <E> PageModel<E> queryForPageVo(String sql, List<Object> args, int pageNo, int pageSize, Class<E> voClass) {
		return queryForPageVo(sql, args.toArray(), pageNo, pageSize, voClass);
	}

	private <E> PageModel<E> queryForPageVo(String sql, Object[] array, int pageNo, int pageSize, Class<E> voClass) {
		//查询数据总条数
		String countSql = buildCountSql(sql);
		int totalRecords = jdbcTemplate.queryForObject(countSql, Integer.class, array);
		
		//查询数据
		sql = buildLimitSql(sql, pageNo, pageSize);
		RowMapper<E> rowMapper = BeanPropertyRowMapper.newInstance(voClass);
		List<E> list = jdbcTemplate.query(sql, array, rowMapper);
		
		if(totalRecords < 1 || CollUtil.isEmpty(list) || totalRecords < list.size()) {
			return null;
		}
		
		//组装PageModel
		PageModel<E> pageModel = new PageModel<>(pageNo, pageSize, totalRecords, list);
		return pageModel;
	}
	
	/**
	 * 将查询返回结果集封装为分页List<Map<K,V>>
	 * 
	 * @return
	 */
	public  PageModel<Map<String, Object>> queryForPageMap(String sql, Object[] array, int pageNo, int pageSize) {
		//查询数据总条数
		String countSql = buildCountSql(sql);
		int totalRecords = jdbcTemplate.queryForObject(countSql, Integer.class, array);
		
		//查询数据
		sql = buildLimitSql(sql, pageNo, pageSize);
		List<Map<String,Object>> list = jdbcTemplate.queryForList(sql, array);
		
		if(totalRecords < 1 || CollUtil.isEmpty(list) || totalRecords < list.size()) {
			return null;
		}
		
		//组装PageModel
		PageModel<Map<String, Object>> pageModel = new PageModel<>(pageNo, pageSize, totalRecords, list);
		return pageModel;
	}

	/**
	 * 重构sql,统计总数
	 * @param sql
	 * @return
	 */
	private String buildCountSql(String sql) {
		StringBuffer countSql = new StringBuffer();
		countSql.append("SELECT COUNT(*) ");
		countSql.append(removeOrders(removeSelect(sql)));
		return countSql.toString();
	}
	
	/**
	 * 去除SELECT *语句，便于SELECT count(*)
	 */
	private String removeSelect(String sql) {
		int beginPosition = sql.toLowerCase().indexOf("from");
		return sql.substring(beginPosition);
	}

	/**
	 * 去除order by 提高select count(*)的速度
	 */
	private String removeOrders(String sql) {
		Matcher m = p.matcher(sql);
		StringBuffer sb = new StringBuffer();
		while (m.find()) {
			m.appendReplacement(sb, "");
		}
		m.appendTail(sb);
		return sb.toString();
	}

	/**
	 * 追加分页查询条件
	 */
	private String buildLimitSql(String sql, int pageNo, int pageSize) {
		StringBuffer sb = new StringBuffer(sql);
		sb.append(" LIMIT " + (pageNo -1) * pageSize + "," + pageSize);
		return sb.toString();
	}

}
